﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Dictionary.Core
{
    public class Source
    {
        public int id { get; set; }
        public String url { get; set; }
        public String regular_exp { get; set; }
        public String description { get; set; }
        public String created { get; set; }
        public String updated { get; set; }
        public int status { get; set; }
        /// <summary>
        /// Default constructor with no parameter
        /// </summary>
        public Source()
        { }
        /// <summary>
        /// Constructor with parameter : URL - REGUILAR_EXP - DESCRIPTION
        /// </summary>
        /// <param name="url">url</param>
        /// <param name="regular_exp">regular_exp</param>
        /// <param name="description">description</param>
        public Source(String url, String regular_exp, String description)
        {
            this.url = url;
            this.regular_exp = regular_exp;
            this.description = description;
        }
        /// <summary>
        /// Method to check is_Exist url source in database
        /// </summary>
        /// <returns>bool</returns>
        public bool isExist()
        {
            int count = 0;
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM source WHERE url = \"" + this.url + "\"";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    count++;
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            if (count > 0)
                return true;
            return false;
        }
        /// <summary>
        /// Method select source form database with id
        /// </summary>
        public void select()
        {
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM source WHERE id = " + this.id;
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    this.id = Convert.ToInt16(dataReader["id"]);
                    this.url = dataReader["url"].ToString();
                    this.regular_exp = dataReader["regular_exp"].ToString();
                    this.description = dataReader["description"].ToString();
                    this.created = dataReader["created"].ToString();
                    this.updated = dataReader["updated"].ToString();
                    this.status = Convert.ToInt16(dataReader["status"]);
                    break;
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
        }
        
        /// <summary>
        /// Method insert new source to database
        /// </summary>
        /// <returns>bool</returns>
        public bool insert()
        {
            if (!this.isExist())
            {
                SQLHelper sqlHelper = new SQLHelper();
                String strInsert = "INSERT INTO source(url,regular_exp,description,created,updated) " +
                                    "VALUES(\"" + this.url + "\",\"" + this.regular_exp + "\",\"" + this.description + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\")";
                if (sqlHelper.executeNonQuery(strInsert) > 0)
                {
                    String strQuery = "SELECT * FROM source WHERE url = '" + this.url + "'";
                    //Create Command
                    MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                    //Create a data reader and Execute the command
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        this.id = Convert.ToInt16(dataReader["id"]);
                        this.url = dataReader["title"].ToString();
                        this.regular_exp = dataReader["regular_exp"].ToString();
                        this.description = dataReader["description"].ToString();
                        this.created = dataReader["created"].ToString();
                        this.updated = dataReader["updated"].ToString();
                        this.status = Convert.ToInt16(dataReader["status"]);
                        break;
                    }
                    return true;
                }
                else
                    return false;
            }
            return false;
        }
        /// <summary>
        /// Method update source
        /// </summary>
        /// <returns>bool</returns>
        public bool update()
        {
            SQLHelper sqlHelper = new SQLHelper();
            String strInsert =  "UPDATE source SET " + 
                                "url = \"" + this.url + "\", " + 
                                "description= \"" + this.description + "\", " +
                                "regular_exp= \"" + this.regular_exp + "\", " + 
                                "updated=\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\" " +
                                "WHERE id = " + this.id;
            if (sqlHelper.executeNonQuery(strInsert) > 0)
            {
                return true;
            }
            else
                return false;
        }
        /// <summary>
        /// Method to delete source
        /// </summary>
        /// <returns>bool</returns>
        public bool delete()
        {
            List<Category> listCategory = Category.selectAll(this.id);
            bool haveError = false;
            foreach (Category item in listCategory)
            {
                if(!item.delete())
                {
                    haveError = true;
                    break;
                }
            }

            if(!haveError)
            {
                SQLHelper sqlHelper = new SQLHelper();
                String strDelete = "DELETE FROM source WHERE id=" + this.id;
                if (sqlHelper.executeNonQuery(strDelete) <= 0)
                    haveError = true;
            }
            return !haveError;
        }

        /// <summary>
        /// Method select all source from database
        /// </summary>
        /// <returns>List<Source></returns>
        public static List<Source> selectAll()
        {
            List<Source> listResult = new List<Source>();
            SQLHelper sqlHelper = new SQLHelper();
            String strQuery = "SELECT * FROM source";
            //Open connection
            if (sqlHelper.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(strQuery, sqlHelper.getConnection());
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                int row = 0;
                while (dataReader.Read())
                {
                    Source temp = new Source();
                    temp.id = Convert.ToInt16(dataReader["id"]);
                    temp.url = dataReader["url"].ToString();
                    temp.regular_exp = dataReader["regular_exp"].ToString();
                    temp.description = dataReader["description"].ToString();
                    temp.created = dataReader["created"].ToString();
                    temp.updated = dataReader["updated"].ToString();
                    temp.status = Convert.ToInt16(dataReader["status"]);
                    row++;
                    listResult.Add(temp);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                sqlHelper.CloseConnection();
            }
            return listResult;
        }
    }
}
